﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;

public partial class tmp_Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            loadData();
            
                        
        }
    }

    private void loadData()
    {
        string sql = @"
SELECT [SubCategoryID]
      ,[CategoryName],[SubCategoryName]      
  FROM SubCategory
  order by [CategoryName];
                            ";

        DataSet ds = DatabaseManager.ExecSQL(sql);
        ddlCategory.Items.Clear();
        ddlCategory.Items.Add(new ListItem("--- Any ----","0"));
        foreach (DataRow dr in ds.Tables[0].Rows)
        {
            try
            {
                ddlCategory.Items.Add(new ListItem(dr["CategoryName"].ToString() + " -- " + dr["SubCategoryName"].ToString(), dr["SubCategoryID"].ToString()));
            }
            catch (Exception ex)
            { }
        }
    }
    protected void btnSearch_Click(object sender, EventArgs e)
    {
        string sql = @"
        SELECT [ArticleID]
,SubCategory.CategoryName
,SubCategory.SubCategoryName
      ,Article.[SubCategoryID]
      ,[Title]
      ,[Address]
      ,[ContactPerson]
      ,[Description]
      ,[City]
      ,[Country]
      ,[Telephone]
      ,[Website]
      ,[Email]
      ,[Fax]
  FROM Article
  inner join SubCategory on SubCategory.SubCategoryID = Article.SubCategoryID
 
where 1=1
        ";
        bool searched=false;
        if (txtBusinessName.Text.Trim() != "")
        {
            searched=true;
            sql += " and Title like '%" + txtBusinessName.Text.Trim() + "%'";
        }

        if (txtLocation.Text.Trim() != "")
        {
            searched=true;
            sql += " and (Address like '%" + txtLocation.Text.Trim() + "%' or City like '%" + txtLocation.Text.Trim() + "%') ";
        }
        if (txtContact.Text.Trim() != "")
        {
            searched = true;
            sql += " and (Telephone like '%" + txtContact.Text.Trim() + "%' or Website like '%" + txtContact.Text.Trim() + "%' or Email like '%" + txtContact.Text.Trim() + "%') ";
        }


        if (ddlCategory.SelectedValue != "0")
        {
            searched=true;
            sql += " and SubCategory.SubCategoryID=" + ddlCategory.SelectedValue;
        }

        sql += @"
         order by SubCategory.CategoryName
,SubCategory.SubCategoryName,[Title]
        ";

        if (searched)
        {
            DataSet ds = DatabaseManager.ExecSQL(sql);
            string html = "<table  width='950px'>";
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                html += "<tr><td>";
                html += @"
  <table border='1' cellspacing='0' cellpadding='3'>
            <tr style='background-color:#D8E1E9'>
                <td>
                    Title</td>
                <td>
                    " + dr["Title"].ToString() + @"</td>
            </tr>
           <tr style='background-color:#B9C3D5'>
                <td>
                    Category</td>
                <td>
                    " +dr["CategoryName"].ToString()+" -- "+ dr["SubCategoryName"].ToString() + @"</td>
            </tr>
            <tr>
                <td>
                    </td>
                <td>
                    <table width='850px' border='1' cellspacing='0' cellpadding='3'>
                        <tr>
                            <td width='125px'>
                                Contact Person</td>
                            <td>
                                " + dr["ContactPerson"].ToString() + @"</td>
                        </tr>
                        <tr>
                            <td>
                                Telephone</td>
                            <td>
                                " + dr["Telephone"].ToString() + @"</td>
                        </tr>
                        <tr>
                            <td>
                                Fax</td>
                            <td>
                                " + dr["Fax"].ToString() + @"</td>
                        </tr>
                        <tr>
                            <td>
                                Website</td>
                            <td>
                                <a href='http://" + dr["Website"].ToString().Replace("http://", "") + @"' target='_blank'>" + dr["Website"].ToString() + @"</a></td>
                        </tr>
                        <tr>
                            <td>
                                Email</td>
                            <td>
                                " +dr["Email"].ToString()+@"</td>
                        </tr>
                        <tr>
                            <td>
                                Address</td>
                            <td>
                                "+dr["Address"].ToString()+@"</td>
                        </tr>
                        <tr>
                            <td>
                                City</td>
                            <td>
                                "+dr["City"].ToString()+@"</td>
                        </tr>
                        <tr>
                            <td>
                                Country</td>
                            <td>
                                " + dr["Country"].ToString() + @"</td>
                        </tr>
                        <tr>
                            <td>
                                Description</td>
                            <td>
                                " + dr["Description"].ToString() + @"</td>
                        </tr>
                    </table>
                </td>
            </tr>
        </table>
";
                html += "</td></tr>";
            }

            lblCount.Text = ds.Tables[0].Rows.Count.ToString() + " Business information found";
            lblResult.Text = html + "</table>";
        }
    }
}